/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package aufgabe2b;

import java.sql.*;


/**
 *
 * @author Dennis Wittkoetter <dennis.wittkoetter@hs-osnabrueck.de>
 */
public class DBConnection {
    private static boolean autocommit = false; 
    private Connection con;
    
    /**
     * 
     * @throws SQLException 
     */
    public DBConnection() throws SQLException {
        DriverManager.registerDriver(new org.apache.derby.jdbc.ClientDriver());
    }
    
    /**
     * 
     * @throws SQLException 
     */
    public void open() throws SQLException {
        System.out.println("Stelle Verbindung her...");
        con = DriverManager.getConnection(
            "jdbc:derby://localhost:1527/Hochschule","dennis", "123");
        System.out.println("Verbindung aufgebaut!");
        
        //fuer Aufgabenteil 2e
        if (autocommit) {
            con.setAutoCommit(false);
            System.out.println("Auto-Commit deaktiviert!");
        }
    }
    
    /**
     * 
     */
    public void close() {
        System.out.println("Schließe Verbindung...");
        try {
            //fuer Aufgabenteil 2e
            if (autocommit) {
                con.setAutoCommit(true);
                con.commit();
                System.out.println("Transaktion ausführen.");
            }
            
            con.close();
            System.out.println("Verbindung geschlossen!");            
        } catch (SQLException e) {
            System.out.println(e);
        }
    }
    
    /**
     * 
     * @param matnr
     * @param name
     * @throws SQLException 
     */
    public void studentNameAendern(int matnr, String name) throws SQLException {
        PreparedStatement ps = con.prepareStatement(
                  "UPDATE Student "
                + "SET name = ? "
                + "WHERE matnr = ?");
        
        ps.setString(1, name);
        ps.setInt(2, matnr);
        int i = ps.executeUpdate();
        
        if (i <= 0) {
            System.out.println("Aktualisierung fehlgeschlagen!");
        }
        else {
            System.out.println("Aktualisierung erfolgreich!");
        }
        
        ps.close();
    }
    
    /**
     * 
     * @return
     * @throws SQLException 
     */
    public void gibStudenten() throws SQLException {       
        String selectString = "SELECT matnr, name FROM Student";
                
        StringBuffer sb = new StringBuffer();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(selectString);

        while (rs.next()) {
            sb.append(rs.getString(1));
            sb.append(": ");
            sb.append(rs.getString(2));
            sb.append('\n');
        }

        System.out.println(sb);
    }
}
